package com.uxsino.commons.utils;

import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;

import lombok.Getter;

@Getter
public class ExcelUtils {

    private Workbook wb;

    private int maxColNum = 0;

    private int maxRowNum = 0;

    private int maxSheetNum = 0;

    private JSONObject content;

    private JSONArray contentByRow;

    private JSONObject errorCollector;

    public void parse() {
        if (wb != null) {
            content = new JSONObject();
            contentByRow = new JSONArray();
            maxSheetNum = wb.getNumberOfSheets();
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                Sheet sheet = wb.getSheetAt(i);
                maxRowNum = maxRowNum > sheet.getLastRowNum() ? maxRowNum : sheet.getLastRowNum();
                for (int j = 0; j <= sheet.getLastRowNum(); j++) {
                    Row row = sheet.getRow(j);
                    if (row == null) {
                        continue;
                    }
                    JSONObject rowDatas = new JSONObject();
                    maxColNum = maxColNum > row.getLastCellNum() - 1 ? maxColNum : row.getLastCellNum() - 1;
                    for (int k = 0; k < row.getLastCellNum(); k++) {
                        Cell cell = row.getCell(k);
                        String val = getCellValue(cell);
                        if (val != null) {
                            content.put(i + "_" + j + "_" + k, val);
                            rowDatas.put(i + "_" + j + "_" + k, val);
                        }
                    }
                    contentByRow.add(rowDatas);
                }
            }
        }
    }

    public ExcelUtils(Workbook workbook) {
        this.wb = workbook;
    }

    /**
     *  使用正则对一列数据验证
     * @param sheetIndex sheet 的Index
     * @param colIndex col 的Index
     * @param regular 正则表达式
     * @param errorMsg 提示的错误信息
     * @return 有错误信息，返回错误所在位置信息 sheetIndex_rowIndex
     */
    public List<String> validata(int sheetIndex, int colIndex, String regular, String errorMsg) {
        List<String> position = Lists.newArrayList();
        JSONObject data = getTargetColDatas(sheetIndex, colIndex);
        Set<String> keySet = data.keySet();
        for (String key : keySet) {
            String val = data.getString(key);
            if (!Pattern.compile(regular).matcher(val).matches()) {
                errorCollector.put(key, "errorMsg");
                position.add(key);
            }
        }
        return position;
    }

    /**
     * 获取一列的所有数据
     * @param sheetIndex sheet 的Index
     * @param colIndex row 的Index
     * @return JSONObject
     */
    public JSONObject getTargetColDatas(int sheetIndex, int colIndex) {
        JSONObject back = new JSONObject();
        for (int i = 0; i < maxRowNum; i++) {
            String key = sheetIndex + "_" + i + "_" + colIndex;
            String val = content.getString(key);
            if (val != null) {
                back.put(key, val);
            }
        }
        if (back.isEmpty()) {
            return null;
        } else {
            return back;
        }
    }

    public void deleteRowFromContentByRow(int sheetIndex, int rowIndex) {
        String keyword = sheetIndex + "_" + rowIndex;
        Iterator<?> it = contentByRow.iterator();
        while (it.hasNext()) {
            JSONObject single = (JSONObject) it.next();
            Set<String> keySet = single.keySet();
            for (String key : keySet) {
                if (keyword.contains(key)) {
                    contentByRow.remove(single);
                    break;
                }
            }
        }
    }

    /**
     * 创建Excel
     * @param title_zh 中文表头
     * @param title_en 英文表头
     * @param sheetName sheet名
     * @param datas 内容
     * @return
     */
    public static Workbook createExcel(String[] title_zh, String[] title_en, String sheetName, JSONArray datas) {
        Workbook workbook = new HSSFWorkbook();
        // sheet创建
        Sheet sheet = workbook.createSheet(sheetName);
        // 样式创建
        CellStyle titleStyle = setCellStyle(workbook, "font_back_weight_loc_left_background_gray");
        CellStyle titleStyle2 = setCellStyle(workbook, "font_back_weight_loc_left_background_alert");
        CellStyle contentStyle = setCellStyle(workbook, "font_back_loc_left");
        int length = title_zh.length; // 整体长度
        int index = 0;
        // 设置表头
        Row firstRow = sheet.createRow(index);
        for (int i = 0; i < length; i++) {
            Cell cell = firstRow.createCell(i);
            cell.setCellValue(title_zh[i]);
            if (title_zh[i].contains("(必填)")) {
                cell.setCellStyle(titleStyle2);
            } else {
                cell.setCellStyle(titleStyle);
            }
            sheet.setColumnWidth(i, 21 * 256); // 设置表格宽度为21个字符宽度
        }
        // 设置内容
        if (datas == null || datas.size() == 0) {
            return workbook;
        }
        Iterator<Object> it = datas.iterator();
        while (it.hasNext()) {
            JSONObject data = (JSONObject) it.next();
            Row contentRow = sheet.createRow(++index);
            for (int j = 0; j < length; j++) {
                String value = data.getString(title_en[j]);
                if (value != null && !value.isEmpty()) {
                    Cell cell = contentRow.createCell(j);
                    cell.setCellValue(value);
                    cell.setCellStyle(contentStyle);
                }
            }
        }
        return workbook;
    }

    /**
     *  获取表格中的内容
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue().getString().trim();
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return TimeUtils.formatTime(cell.getDateCellValue());
            } else {
                DecimalFormat df = new DecimalFormat("#.##");
                return df.format(cell.getNumericCellValue());
            }
        case Cell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        case Cell.CELL_TYPE_FORMULA:
            return cell.getCellFormula();
        default:
            return null;
        }
    }

    /**
     * 下载excel文件
     * @param workbook
     * @param response
     * @param fileName
     */
    public static void downLoad(Workbook workbook, HttpServletResponse response, String fileName) {
        try {
            response.setContentType("application/xls;charset=ISO8859-1");
            response.setHeader("Content-Disposition",
                "attachment;fileName=" + new String((fileName + ".xls").getBytes("UTF-8"), "ISO8859-1"));
            OutputStream os = response.getOutputStream();
            workbook.write(os);
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建并按参数设置表格的样式，并返回该样式
     * @param wb
     * @param type
     * @return
     */
    public static CellStyle setCellStyle(Workbook wb, String type) {
        CellStyle cellStyle = wb.createCellStyle();
        if (type.contains("font")) {
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 11);
            if (type.contains("back")) {
                font.setFontName("微软雅黑");
            } else if (type.contains("song")) {
                font.setFontName("宋体");
            }
            if (type.contains("green")) {
                font.setColor(HSSFColor.GREEN.index);
            } else if (type.contains("red")) {
                font.setColor(HSSFColor.RED.index);
            }
            if (type.contains("weight")) {
                font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度
            }
            cellStyle.setFont(font);
        }
        if (type.contains("loc")) {
            if (type.contains("left")) {
                cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            } else if (type.contains("middle")) {
                cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            }
        }
        if (type.contains("background")) {
            String subType = type.split("background")[1];
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            if (subType.contains("alert")) {
                cellStyle.setFillForegroundColor(HSSFColor.RED.index);
            } else if (subType.contains("gray")) {
                cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
            }
        }
        return cellStyle;
    }

    /**
     * 设置数据有效性（下拉等）
     */
    public static Sheet DataValidation(Sheet sheet, JSONObject dropDown) {
        Set<String> keySet = dropDown.keySet();
        for (String key : keySet) {
            int firstRow = Integer.parseInt(key.split("_")[0]);
            int endRow = Integer.parseInt(key.split("_")[1]);
            int firstCol = Integer.parseInt(key.split("_")[2]);
            int endCol = Integer.parseInt(key.split("_")[3]);
            String text = dropDown.getString(key);
            // 加载下拉列表内容
            DVConstraint constraint = DVConstraint.createExplicitListConstraint(text.split(","));
            // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
            // 数据有效性对象
            HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
            sheet.addValidationData(data_validation_list);
        }
        return sheet;
    }

    /**
     * 生成错误报告
     */
    public static Workbook buildReport(Workbook workbook, JSONObject errorMsgs, int dataRow) {
        // 通过 字体样式
        CellStyle passStyle = ExcelUtils.setCellStyle(workbook, "font_back_green_loc_left");
        // 错误 字体样式
        CellStyle errStyle = ExcelUtils.setCellStyle(workbook, "font_back_red_loc_left");

        // 按顺序遍历文件
        // 对errorMsgs的JSON进行重组,格式sheet_row:col_message;
        JSONObject newErrorMsgs = new JSONObject();
        for (String key : errorMsgs.keySet()) {
            String[] poi = key.split("_");
            if (newErrorMsgs.getString(poi[0] + "_" + poi[1]) != null) {
                continue;
            }
            newErrorMsgs.put(poi[0] + "_" + poi[1], poi[2] + "_" + errorMsgs.getString(key));
        }
        for (int k = 0; k < workbook.getNumberOfSheets(); k++) {
            Sheet sheet = workbook.getSheetAt(k);
            if (sheet.getRow(0) == null) {
                continue;
            }
            int maxColNum = sheet.getRow(0).getLastCellNum();
            for (int i = dataRow; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                String errorMsg = "";
                boolean isErrorRow = false;// 判断是否错误行
                if (row == null) {
                    break;
                }
                if (newErrorMsgs.getString(k + "_" + i) == null) {
                    isErrorRow = false;
                } else if (Integer.parseInt(newErrorMsgs.getString(k + "_" + i).split("_")[0]) < 0) {
                    isErrorRow = true;
                    errorMsg = newErrorMsgs.getString(k + "_" + i).split("_")[1];
                } else {
                    for (int j = 0; j < maxColNum; j++) {
                        Cell oldCell = row.getCell(j);
                        String key = k + "_" + i + "_" + j;
                        if (errorMsgs.getString(key) != null) {
                            if (oldCell != null) {
                                oldCell.setCellStyle(errStyle);
                            }
                            errorMsg = errorMsgs.getString(key);
                            isErrorRow = true;
                            break;
                        }
                    }
                }
                Cell newCell = null;
                if (dataRow > 1) {
                    newCell = row.createCell(maxColNum);
                } else {
                    newCell = row.createCell(maxColNum + 2); // 反馈信息设置为最后+2格位置
                }
                if (isErrorRow) {
                    newCell.setCellValue(errorMsg);
                    newCell.setCellStyle(errStyle);
                } else {
                    newCell.setCellValue("导入成功");
                    newCell.setCellStyle(passStyle);
                }
            }
            sheet.protectSheet("edit");// 锁定单元格，使其无法被修改
        }
        return workbook;
    }

    // 单元格合并
    public static Cell cellMerge(Sheet sheet, String range, String value) {
        String[] arr = range.split(",");
        int firstRow = Integer.parseInt(arr[0]);
        int lastRow = Integer.parseInt(arr[1]);
        int firstCol = Integer.parseInt(arr[2]);
        int lastCol = Integer.parseInt(arr[3]);
        Row row = sheet.getRow(firstRow) == null ? sheet.createRow(firstRow) : sheet.getRow(firstRow);
        for (int i = 0; i <= lastCol - firstCol; i++) {
            Cell cell = row.createCell(firstCol + i);
            if (i == 0) {
                cell.setCellValue(value);
            }
        }
        CellRangeAddress range1 = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(range1);
        return row.getCell(firstCol);
    }
}
